﻿using Sales.Data.Access.Entities;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace Sales.Data.Access.Persistence
{
    public class ClienteService : PersistenceFactory<ClienteService>
    {
        public List<Cliente> ListarClientes() 
        {
            List<Cliente> _Return = new List<Cliente>();
            DataTable _Table = new DataTable();

            using(SqlConnection _Connection = new SqlConnection())
            {
                _Connection.ConnectionString = ConfigurationManager.ConnectionStrings["Sales"].ConnectionString;

                using (SqlCommand _Command = _Connection.CreateCommand()) 
                {
                    _Command.Connection = _Connection;
                    _Command.CommandType = CommandType.Text;
                    _Command.CommandText = "select * from Clientes";

                    _Command.Connection.Open();

                    using (SqlDataReader _Reader = _Command.ExecuteReader()) 
                    {
                        _Table.Load(_Reader);

                        foreach (DataRow _Row in _Table.Rows)
                        {
                            _Return.Add(new Cliente(_Row));
                        }
                    }
                }
            }

            return _Return;
        }

        public Cliente BuscaCliente(int _Id) 
        {
            DataTable _Table = new DataTable();
            Cliente _Return = null;

            using (SqlConnection _Connection = new SqlConnection())
            {
                _Connection.ConnectionString = ConfigurationManager.ConnectionStrings["Sales"].ConnectionString;

                using (SqlCommand _Command = _Connection.CreateCommand())
                {
                    _Command.Connection = _Connection;
                    _Command.CommandType = CommandType.Text;
                    _Command.CommandText = string.Format("select * from Clientes where Id = {0}", _Id);

                    _Command.Connection.Open();

                    using (SqlDataReader _Reader = _Command.ExecuteReader())
                    {
                        _Table.Load(_Reader);

                        if (_Table.Rows.Count > 0)
                        {
                            _Return = new Cliente(_Table.Rows[0]);
                        }
                    }
                }
            }

            return _Return;
        }

        public bool AdicionaCliente(Cliente _Cliente) 
        {
            bool _Return = false;

            using (SqlConnection _Connection = new SqlConnection())
            {
                _Connection.ConnectionString = ConfigurationManager.ConnectionStrings["Sales"].ConnectionString;

                using (SqlCommand _Command = _Connection.CreateCommand())
                {
                    _Command.Connection = _Connection;
                    _Command.CommandType = CommandType.Text;
                    _Command.CommandText = string.Format("insert into Clientes(Nome, CPF, RG) values('{0}', '{1}', '{2}')", _Cliente.Nome, _Cliente.CPF, _Cliente.RG);

                    _Command.Connection.Open();

                    using (SqlDataReader _Reader = _Command.ExecuteReader())
                    {
                        _Reader.Close();
                        _Return = _Command.ExecuteNonQuery() > 0 ? true : false;
                    }
                }
            }

            return _Return;
        }

        public bool AlteraCliente(Cliente _Cliente) 
        {
            bool _Return = false;

            using (SqlConnection _Connection = new SqlConnection())
            {
                _Connection.ConnectionString = ConfigurationManager.ConnectionStrings["Sales"].ConnectionString;

                using (SqlCommand _Command = _Connection.CreateCommand())
                {
                    _Command.Connection = _Connection;
                    _Command.CommandType = CommandType.Text;
                    _Command.CommandText = string.Format("update Clientes set Nome = '{1}', CPF = '{2}', RG = '{3}' where Id = {0}", _Cliente.Id, _Cliente.Nome, _Cliente.CPF, _Cliente.RG);

                    _Command.Connection.Open();

                    using (SqlDataReader _Reader = _Command.ExecuteReader())
                    {
                        _Reader.Close();
                        _Return = _Command.ExecuteNonQuery() > 0 ? true : false;
                    }
                }
            }

            return _Return;
        }

        public bool ExcluiCliente(Cliente _Cliente) 
        {
            bool _Return = false;

            using (SqlConnection _Connection = new SqlConnection())
            {
                _Connection.ConnectionString = ConfigurationManager.ConnectionStrings["Sales"].ConnectionString;

                using (SqlCommand _Command = _Connection.CreateCommand())
                {
                    _Command.Connection = _Connection;
                    _Command.CommandType = CommandType.Text;
                    _Command.CommandText = string.Format("delete from Clientes where Id = {0}", _Cliente.Id);

                    _Command.Connection.Open();

                    using (SqlDataReader _Reader = _Command.ExecuteReader())
                    {
                        _Reader.Close();
                        _Return = _Command.ExecuteNonQuery() > 0 ? true : false;
                    }
                }
            }

            return _Return;
        }
    }
}
